"""
Case Type   : GSC功能模块
Case Name   : gsc打开，lsc阈值设置较小，并发进行ustore宽表相关操作
Create At   : 2024/10/26
Owner       : @lonely-dance
Description :
    1.配置数据库参数，设置local_syscache_threshold为1MB
    2.重启数据库生效
    3.创建ustore宽表，并插入1条数据
    4.循环进行alter ustore宽表操作
    5.步骤4同时，多线程并发对ustore宽表循环进行查询
    6.清理环境
Expect      :
    1.修改参数成功
    2.重启成功
    3.执行成功
    4.成功
    5.成功
    6.成功
History     :
"""
import os
import unittest

from testcase.utils.ComThread import ComThread
from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node
from yat.test import macro

pri_sh = CommonSH('PrimaryDbUser')
node_num = pri_sh.get_node_num()


@unittest.skipIf(node_num < 3, '非1主2备环境不执行')
class GscTestCase(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.log.info(f'----{os.path.basename(__file__)} start----')
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.tb_name = 'tb_gsc0066'
        self.com = Common('PrimaryDbUser')
        self.constant = Constant()
        self.p_node = Node('PrimaryDbUser')
        self.init_para1 = None
        self.init_para2 = None
        self.ass1 = False
        self.ass2 = True

    def test_GSC(self):
        text = '----step1:配置数据库参数;----'
        self.log.info(text)
        status_result = self.pri_sh.get_db_cluster_status('status')
        self.assertTrue(status_result, '执行失败:' + text)
        text = '----查看初始配置值;----'
        self.log.info(text)
        self.init_para1 = self.com.show_param('enable_global_syscache')
        self.init_para2 = self.com.show_param('local_syscache_threshold')
        self.log.info(self.init_para1)

        result = self.pri_sh.execute_gsguc('set',
                                           self.constant.GSGUC_SUCCESS_MSG,
                                           f"enable_global_syscache=on")
        self.log.info(result)
        self.assertTrue(result, '执行失败:' + text)
        result = self.pri_sh.execute_gsguc('set',
                                           self.constant.GSGUC_SUCCESS_MSG,
                                           f"local_syscache_threshold=1MB")
        self.log.info(result)
        self.assertTrue(result, '执行失败:' + text)

        text = '----step2:重启数据库生效; expect:重启成功----'
        self.log.info(text)
        restart = self.pri_sh.restart_db_cluster()
        self.assertTrue(restart, '执行失败:' + text)

        text = f'----step3:创建ustore宽表; expect:执行成功----'
        self.log.info(text)
        cmd = f"""
            drop table if exists {self.tb_name};
            create table {self.tb_name} (col1 money,  col2 boolean, col3 CHAR(4), 
            col4 CHARACTER(4), col5 NCHAR(4), col6 VARCHAR(10), col7 CHARACTER VARYING(10), 
            col8 VARCHAR2(10), col9 NVARCHAR2(10), col10 TEXT, col11 CLOB, col12 BLOB, 
            col13 RAW, col14 BYTEA, col15 DATE, col16 TIME, col17 time with time zone, col18 timestamp, 
            col19 timestamp with time zone, col20 SMALLDATETIME, col21 abstime, col22 cidr, col23 inet, 
            col24 macaddr, col25 BIT(3), col26 BIT VARYING(5), col27 UUID, col28 tsvector, col29 tsquery, 
            set hll(14), col33 int4range, col34 int8range, col35 numrange, col36 tsrange, col37 tstzrange, 
            col38 daterange, col39 HASH16, col40 HASH32); 
            insert into {self.tb_name} values ('52093.89'::money, true, 'aa', 
            'aa', 'aa', 'aa', 'aa', 'aa', 'aa', 'aa', 'aa', empty_blob(), HEXTORAW('DEADBEEF'), E'\\xDEADBEEF', 
            '1900-01-01 00:00:00', '1900-01-01 00:00:00 pst', '1900-01-01 00:00:00', '1900-01-01 00:00:00 pst', 
            '1900-01-01 00:00:00', '1900-01-01 00:00:00', '1910-01-01 00:00:00', '10.10.10.10', '10.10.10.10', 
            '08002b010203', B'10'::bit(3), B'00', 'a0eebc999c0b4ef8bb6d6bb9bd380a11', 'test', 'test', 
            hll_empty(14,-1), '[1,10]', '[1,10]', '[0.00, 10.00]', 
            '[1900-01-01 00:00:00, 2020-01-01 00:00:00]', '[1900-01-01 00:00:00 pst, 2020-01-01 00:00:00 pst]', 
            '(1900-01-01, 2020-01-01)','ffff', 'ffffffffffffffffffffffffffffffff');
        """
        self.log.info(cmd)
        res = self.pri_sh.execut_db_sql(cmd)
        self.log.info(res)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, res, '执行失败:' + text)
        self.assertIn('INSERT 0 1', res, '执行失败:' + text)

        text = '----step4:循环进行alter ustore宽表操作;; expect:成功----'
        self.log.info(text)
        cmd = f"""
            drop table if exists {self.tb_name};
            create table {self.tb_name} (col1 money,  col2 boolean, col3 CHAR(4), 
            col4 CHARACTER(4), col5 NCHAR(4), col6 VARCHAR(10), col7 CHARACTER VARYING(10), 
            col8 VARCHAR2(10), col9 NVARCHAR2(10), col10 TEXT, col11 CLOB, col12 BLOB, 
            col13 RAW, col14 BYTEA, col15 DATE, col16 TIME, col17 time with time zone, col18 timestamp, 
            col19 timestamp with time zone, col20 SMALLDATETIME, col21 abstime, col22 cidr, col23 inet, 
            col24 macaddr, col25 BIT(3), col26 BIT VARYING(5), col27 UUID, col28 tsvector, col29 tsquery,  
            set hll(14), col33 int4range, col34 int8range, col35 numrange, col36 tsrange, col37 tstzrange, 
            col38 daterange, col39 HASH16, col40 HASH32); 
            insert into {self.tb_name} values ('52093.89'::money, true, 'aa', 
            'aa', 'aa', 'aa', 'aa', 'aa', 'aa', 'aa', 'aa', empty_blob(), HEXTORAW('DEADBEEF'), E'\\xDEADBEEF', 
            '1900-01-01 00:00:00', '1900-01-01 00:00:00 pst', '1900-01-01 00:00:00', '1900-01-01 00:00:00 pst', 
            '1900-01-01 00:00:00', '1900-01-01 00:00:00', '1910-01-01 00:00:00', '10.10.10.10', '10.10.10.10', 
            '08002b010203', B'10'::bit(3), B'00', 'a0eebc999c0b4ef8bb6d6bb9bd380a11', 'test', 'test', 
            hll_empty(14,-1), '[1,10]', '[1,10]', '[0.00, 10.00]', 
            '[1900-01-01 00:00:00, 2020-01-01 00:00:00]', '[1900-01-01 00:00:00 pst, 2020-01-01 00:00:00 pst]', 
            '(1900-01-01, 2020-01-01)','ffff', 'ffffffffffffffffffffffffffffffff');
            select * from {self.tb_name};
        """
        thread_1 = ComThread(self.pri_sh.cycle_exec_sql, args=(cmd, 1000, False, None, True))
        thread_1.setDaemon(True)
        thread_1.start()
        thread_1.join(300)

        text = '----step5:多线程并发对ustore宽表循环进行查询; expect:成功----'
        self.log.info(text)
        cmd = f"select * from {self.tb_name}; "
        self.log.info(cmd)
        select_thread_num = 4
        connect_thread = []
        for i in range(select_thread_num):
            connect_thread.append(ComThread(self.pri_sh.cycle_exec_sql, args=(
                cmd, 120, False, None, True)))
            connect_thread[i].setDaemon(True)
            connect_thread[i].start()

        res1 = thread_1.get_result()
        self.log.info(res1)
        for res in res1:
            if res.endswith('ERROR'):
                self.ass1 = False
                return self.ass1
        for i in range(select_thread_num):
            connect_thread[i].join(300)
            result = connect_thread[i].get_result()
            self.log.info(result)
            for res in result:
                if res.endswith('(1 row)'):
                    self.ass2 = True
                    return self.ass2

        self.assertTrue(self.ass1, '执行失败:' + text)
        self.assertTrue(self.ass2, '执行失败:' + text)

    def tearDown(self):
        text = '----step6:清理环境; expect:成功----'
        self.log.info(text)
        cmd = f"""
            drop table if exists {self.tb_name};
        """
        res = self.pri_sh.execut_db_sql(cmd)
        self.log.info(res)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, res, '执行失败:' + text)

        text = '----恢复数据库参数----'
        self.log.info(text)
        result = self.pri_sh.execute_gsguc('set',
                                           self.constant.GSGUC_SUCCESS_MSG,
                                           f"enable_global_syscache={self.init_para1}")
        self.assertTrue(result, '执行失败:' + text)

        result = self.pri_sh.execute_gsguc('set',
                                           self.constant.GSGUC_SUCCESS_MSG,
                                           f"local_syscache_threshold={self.init_para2}")
        self.assertTrue(result, '执行失败:' + text)

        text = '----重启数据库----'
        self.log.info(text)
        restart = self.pri_sh.restart_db_cluster()
        self.assertTrue(restart, '执行失败:' + text)

        self.log.info(f'----{os.path.basename(__file__)} end----')
